package JDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO extends BaseDAO {

    //添加的方法
    public void add(Student student) throws SQLException, ClassNotFoundException {
        Connection con = null;
        PreparedStatement ps = null;//预编译对象
        String sql = "insert int student(cid,sname,sex) values(?,?,?)";
        try {
            con = this.getCon();
            ps = con.prepareStatement(sql);
            //设置占位符对应的参数
            ps.setInt(1, student.getCid());
            ps.setString(2, student.getSex());
            ps.setString(3, student.getSex());
            ps.executeUpdate();
        } finally {
            this.close(con, ps, null);
        }
    }

    public void update(Student student) throws SQLException, ClassNotFoundException {
        Connection con = null;
        PreparedStatement ps = null;//预编译对象
        String sql = "update student set cid =?,sname=?,sex=? where sid=?";
        try {
            con = this.getCon();
            ps = con.prepareStatement(sql);
            //设置占位符对应的参数
            ps.setInt(1, student.getCid());
            ps.setString(2, student.getSex());
            ps.setString(3, student.getSex());
            ps.setInt(4, student.getSid());
            ps.executeUpdate();
        } finally {
            this.close(con, ps, null);
        }
    }

    public void delete(int sid) throws SQLException, ClassNotFoundException {
        Connection con = null;
        PreparedStatement ps = null;//预编译对象
        String sql = "DELETE  FROM  student WHERE stuid = ?";
        try {
            con = this.getCon();
            ps = con.prepareStatement(sql);
            //设置占位符对应的参数
            ps.setInt(1, sid);

            ps.executeUpdate();
        } finally {
            this.close(con, ps, null);
        }
    }

    public List<Student> findAll() throws SQLException, ClassNotFoundException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<Student>();
        String sql="select * from Student";
        try {
            con = this.getCon();
            ps=con.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Student s =new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4));
                list.add(s);
            }
        } finally {
            this.close(con, ps, null);
        }
        return list;
    }

    public Student findByName(String name)throws SQLException, ClassNotFoundException{
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql="select * from Student WHERE sname =?";
        Student s =null;
        try {
            con = this.getCon();
            ps=con.prepareStatement(sql);
            ps.setString(1,name);
            rs=ps.executeQuery();
            while (rs.next()){
                 s =new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4));
            }
        } finally {
            this.close(con, ps, null);
        }
        return s;
    }

}
